﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using CWB.Common.Constant;
using CWB.Common.DTO;
using CWB.Common.Logging;
using CWB.Common.Utilities;
using CWB.Web.Models;
using System.Runtime.Serialization;
using System.Linq;
using CWB.Web.CacheLayer;

namespace CWB.Web.DB
{
    public class DbModulePlaceOrder
    {
        public int AgentSN;
        public int ProdItemSN;
        public int Unit;
        public int Price;
        public OrderPosition Position;
    }

    #region Request models

    [DataContract]
    public class DbModuleRequestOrder
    {
        [DataMember(Name = "sn")]
        public int sn;
        public int AgentSN;
        [DataMember(Name = "psn")]
        public int ProdItemSN;
        [DataMember(Name = "num")]
        public int Unit;
        [DataMember(Name = "pr")]
        public int Price;
        [DataMember(Name = "ct")]
        public DateTime CreateDateTime;
        [DataMember(Name = "ut")]
        public DateTime UpdateDateTime;
        [DataMember(Name = "s")]
        public mStatus Status;
        [DataMember(Name = "of")]
        public mOrderFrom orderFrom;
        [DataMember(Name = "ps")]
        public mPosition Position;

        public class mPosition : IdName { }
        public class mStatus : IdName { }
        public class mOrderFrom : IdName { }

    }

    #endregion

    public class DbModuleOrder
    {
        public int OrderSN;
        public int AgentSN;
        public AgentProdItem ProdItem;
    }

    public class DbModuleOrderDetail
    {
        public OrderPosition Position;
        public int AskPrice;
        public int ExecPrice;
        public DateTime AskTime;
        public DateTime ExecTime;
    }

    public class DbTableRequestOrder : BaseDBA
    {
        private readonly static ISysLog log = SysLog.GetLogger(typeof(DbTableRequestOrder));

        public DbTableRequestOrder()
        {
            base.init(typeof(DbTableRequestOrder).ToString(), DataBaseName.Org);
        }

        private List<DbModuleRequestOrder> dr2DbModuleRequestOrder(SQLiteDataReader sdr)
        {
            List<DbModuleRequestOrder> listbbu = new List<DbModuleRequestOrder>() { };
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    listbbu.Add(new DbModuleRequestOrder()
                    {
                        AgentSN = Convert.ToInt16(sdr["AgentSN"].ToString()),
                        Price = Convert.ToInt16(sdr["Price"].ToString()),
                        CreateDateTime = Convert.ToDateTime(sdr["CreateDateTime"].ToString()),
                        UpdateDateTime = Convert.ToDateTime(sdr["UpdateDateTime"].ToString()),
                        orderFrom = new DbModuleRequestOrder.mOrderFrom()
                        {
                            id = sdr["OrderFrom"].ToString(),
                            name = ((OrderFrom)Enum.Parse(typeof(OrderFrom), sdr["OrderFrom"].ToString())).ToString()
                        },
                        Position = new DbModuleRequestOrder.mPosition()
                        {
                            id = sdr["Position"].ToString(),
                            name = ((OrderPosition)Enum.Parse(typeof(OrderPosition), sdr["OrderFrom"].ToString())).ToString()
                        },
                        ProdItemSN = Convert.ToInt16(sdr["ProdItemSN"].ToString()),
                        sn = Convert.ToInt16(sdr["sn"].ToString()),
                        Status = new DbModuleRequestOrder.mStatus()
                        {
                            id = sdr["Status"].ToString(),
                            name = ((RequestStatus)Enum.Parse(typeof(RequestStatus), sdr["Status"].ToString())).ToString()
                        },
                        Unit = Convert.ToInt16(sdr["Unit"].ToString())
                    });
                }
            }
            return listbbu;
        }

        public PlaceRequestResult CreateRequestOrder(DbModulePlaceOrder model)
        {
            //Check hierarchy
            DbTableAgent dbagent = new DbTableAgent();
            bool chkHierarchyPassed = dbagent.chkIsInHierarchy(model.AgentSN);
            PlaceRequestResult rtn = PlaceRequestResult.Empty;
            if (chkHierarchyPassed)
            {
                try
                {
                    //insert into RequestOrder
                    string sql = @"
                        insert into RequestOrder 
                        (Status, OrderFrom, AgentSN, ProdItemSN, Position, Unit, Price, CreateDateTime, UpdateDateTime)
                        values
                        (@Status, @OrderFrom, @AgentSN, @ProdItemSN, @Position, @Unit, @Price, @CreateDateTime,@UpdateDateTime)";
                    SqlCommandObj sco = new SqlCommandObj()
                    {
                        commandText = sql,
                        paraList = new List<SQLiteParameter>()
                            {
                                new SQLiteParameter("@Status", (int)RequestStatus.New),
                                new SQLiteParameter("@OrderFrom", 1),
                                new SQLiteParameter("@AgentSN", model.AgentSN),
                                new SQLiteParameter("@ProdItemSN", model.ProdItemSN),
                                new SQLiteParameter("@Position", (int)model.Position),
                                new SQLiteParameter("@Unit", model.Unit),
                                new SQLiteParameter("@Price", model.Price),
                                new SQLiteParameter("@CreateDateTime", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")),
                                new SQLiteParameter("@UpdateDateTime", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"))
                            }
                    };
                    int exenums = ExecuteNonQuery(sco);
                    if (exenums > 0)
                    {
                        rtn = PlaceRequestResult.Success;
                    }
                    else
                    {
                        rtn = PlaceRequestResult.GeneralError;
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    rtn = PlaceRequestResult.GeneralError;
                }
            }
            else
            {
                rtn = PlaceRequestResult.NotInHierarchy;
            }
            return rtn;
        }

        private bool CreateNewOrder(int AgentSN, int ProdItemSN, OrderPosition Position, int AskPrice, int Unit)
        {
            bool rtn = false;
            try
            {
                startTrain();
                //insert into RequestOrder
                string sql = @"
                    insert into RequestOrder 
                    (Status, OrderFrom, AgentSN, ProdItemSN, Position, Unit, Price, CreateDateTime)
                    values
                    (@Status, @OrderFrom, @AgentSN, @ProdItemSN, @Position, @Unit, @Price, @CreateDateTime)";
                SqlCommandObj sco = new SqlCommandObj()
                {
                    commandText = sql,
                    paraList = new List<SQLiteParameter>()
                    {
                        new SQLiteParameter("@AgentSN", AgentSN),
                        new SQLiteParameter("@ProdItemSN", ProdItemSN),
                        new SQLiteParameter("@AgentSN", AgentSN),
                        new SQLiteParameter("@ProdItemSN", ProdItemSN)
                    }
                };


                sco = new SqlCommandObj()
                {
                    commandText = "insert into PlaceOrder (AgentSN, ProdItemSN, OrderFrom) values (@AgentSN, @ProdItemSN, 1)",
                    paraList = new List<SQLiteParameter>()
                    {
                        new SQLiteParameter("@AgentSN", AgentSN),
                        new SQLiteParameter("@ProdItemSN", ProdItemSN)
                    }
                };
                ExecuteNonQuery(sco);
                //get next PK
                sco.commandText = "select last_insert_rowid();";
                sco.paraList = null;
                int sn = ExecuteCount(sco);

                for (int i = 0; i < Unit; i++)
                {
                    sql = @"
                        insert into PlaceOrderDetail (AskCreateDateTime, AskPrice, Position, PlaceOrderSn)
                        values (@AskCreateDateTime, @AskPrice, @Position, @PlaceOrderSn)
                    ";

                    sco = new SqlCommandObj()
                    {
                        commandText = sql,
                        paraList = new List<SQLiteParameter>()
                            {
                                new SQLiteParameter("@Position", (int)Position),
                                new SQLiteParameter("@PlaceOrderSn", sn),
                                new SQLiteParameter("@AskPrice", AskPrice),
                                new SQLiteParameter("@AskTAskCreateDateTimeime", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"))
                            }
                    };
                    ExecuteNonQuery(sco);
                }
                commitTrain();
                rtn = true;
            }
            catch (Exception ex)
            {
                rollbackTrain();
                log.Debug(ex.Message);
            }
            return rtn;
        }

        public List<DbModuleRequestOrder> SearchRequestOrder(int iUnderAgentSN, string searchValue, DateTime idtFrom, DateTime idtto, DBSortingPara dbSortingPara, ref Paging pg)
        {
            List<DbModuleRequestOrder> rtn = new List<DbModuleRequestOrder>() { };

            //Check hierarchy
            DbTableAgent dbag = new DbTableAgent();
            bool chkHierarchyPassed = dbag.chkIsInHierarchy(iUnderAgentSN);
            idtto = idtto.AddDays(1);

            if (chkHierarchyPassed)
            {
                DbTableAgent dta = new DbTableAgent();
                List<int> sns = dta.getAllDownLineSN(iUnderAgentSN, 3, searchValue);

                string strSNs = string.Join(",", sns.Select(x => x.ToString()).ToArray());
                string sqlMain = "Select * from RequestOrder";
                string sqlWhere = string.Format(" where AgentSN in ({0}) and UpdateDateTime >= @dtStart and UpdateDateTime < @dtEnd ", strSNs);

                string sqlOrderby = default(string);
                List<SQLiteParameter> sqlPara = new List<SQLiteParameter>() { };

                sqlOrderby = dbSortingPara.getOrderByHLQ();

                //get totalRecord
                SqlCommandObj sco = new SqlCommandObj()
                {
                    paraList = new List<SQLiteParameter>()
                            {
                                new SQLiteParameter("@AgentSN", iUnderAgentSN),
                                new SQLiteParameter("@dtStart", DateTimeHelper.getDateTimeStringForSqlite(idtFrom)),
                                new SQLiteParameter("@dtEnd", DateTimeHelper.getDateTimeStringForSqlite(idtto))
                            }
                };
                sco.commandText = sqlMain.Replace(" * ", " count(*) as rcnt ") + sqlWhere;
                int count = ExecuteCount(sco);
                pg.totalRecord = count;

                //get data
                sco.commandText = string.Format("{0} {1} {2} {3}", sqlMain, sqlWhere, sqlOrderby, pg.getLimitSql());
                using (SQLiteDataReader dr = ExecuteReader(sco))
                {
                    rtn = dr2DbModuleRequestOrder(dr);
                }
            }
            return rtn;
        }
    }
}
